package message.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import db.dbConnection;
import message.bean.Message;

public class MessageService {
  dbConnection db=new dbConnection();
  
  public List ShowAllMessage(int pagenow,int pagesize){
	  List<Message> list=new ArrayList();
	  String sql="select * from message limit ?,?";
	  Connection conn=db.getConnection();  
	  try {
			PreparedStatement past = conn.prepareStatement(sql);
			past.setInt(1, pagenow*pagesize);
			past.setInt(2, pagesize);
			ResultSet rs = past.executeQuery();
			while (rs.next()) {
				Message message=new Message();
				message.setContent(rs.getString("content"));
				message.setId(rs.getInt("id"));
				message.setUsername(rs.getString("username"));
				message.setAddr(rs.getString("addr"));
				message.setTel(rs.getString("tel"));
				message.setEmail(rs.getString("email"));
				
				list.add(message);
			}		
			rs.close();
			past.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
	  return list;
  }
  public int CountAllMessage(){
	  int count=0;
	  String sqlString="select count(*) from message ";
	 Connection conn=db.getConnection();
	 try{
		 PreparedStatement past=conn.prepareStatement(sqlString);
		 ResultSet rSet=past.executeQuery();
		 while(rSet.next()){
			 count=rSet.getInt(1);
		 }
	 }catch(Exception e){
		 e.printStackTrace();
	 }
	  return count; 	
  }
  public boolean AddMessage(Message message){
	 String username=message.getUsername();
	 String addr=message.getAddr();
	 String email=message.getEmail();
	 String tel=message.getTel();
	  String content=message.getContent();
	  content=content.replace("\n","<br/>");
	  content=content.replace(" ", "&nbsp;&nbsp;");
	  content=content.replace("m³", "m&sup3;");
	  Date d = new Date();
	    DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
	    String str=df.format(d);
	    System.out.print(str);
	  boolean flag = false;
	  Connection conn = db.getConnection();
		try {
			PreparedStatement past = null;
			String sql = "insert into message(username,addr,email,tel,content,time) values ("
					+ "'" + username
					+ "'" + "," + "'" + addr + "'" + ","+"'"+ email+ "'"+","+"'"+tel+"'"+","+"'"+content+"'"+","+"'"+str+"'"+")";
			past = conn.prepareStatement(sql);
			past.executeUpdate(sql);
			flag = true;
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
  }
  
  public Message ShowoneMessage(Message messagebean){
	  Message message=new Message();
	  String sql="select * from message where id=?";
			  Connection conn=db.getConnection();
	  		  PreparedStatement past=null;
	  		try {
				past = conn.prepareStatement(sql);
				past.setInt(1, messagebean.getId());
				ResultSet rs = past.executeQuery();
				if (!rs.next()) {
					return null;
				}
				message.setContent(rs.getString("content"));
				message.setId(rs.getInt("id"));
				message.setUsername(rs.getString("username"));
				message.setAddr(rs.getString("addr"));
				message.setTel(rs.getString("tel"));
				message.setEmail(rs.getString("email"));
				message.setTime(rs.getString("time"));
				rs.close();
				past.close();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				db.closeConnection(conn);
			}
			return message;
		}
	  public boolean DeleteMessage(Message messagebean){
		  boolean flag = false;
			Connection conn = db.getConnection();
			String sql1 = "delete from message where id =" + "'"
					+ messagebean.getId() + "'";
			
			try {
				PreparedStatement past = null;
				past = conn.prepareStatement(sql1);
				past.executeUpdate(sql1);
				flag = true;
				past.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				db.closeConnection(conn);
			}
			return flag;
	  }
	
	  
  }

